import com.vca.common.constants.Constants;
import com.vca.common.utils.DateUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

public class ExcelExporterTpCvs {

    public static final String selectOrder = "SELECT `id`, `order_id`, `uid`, `freight_price`, `total_num`, `total_price`, `total_postage`, `pay_price`, `pay_integral`, `integral_id`, `pay_postage`, `deduction_price`, `coupon_id`, `coupon_price`, `paid`, `pay_type`, `pay_time`, `create_time`, `status`, `return_order_no`, `return_waybill_no`, `return_user_name`, `return_user_phone`, `return_user_address`, `agreed_return_time`, `pick_up_date`, `pick_up_start_time`, `pick_up_end_time`, `refund_status`, `refund_reason_wap_img`, `refund_reason_wap_explain`, `refund_reason_wap`, `refund_reason`, `refund_application_time`, `refund_reason_time`, `refund_price`, `delivery_name`, `delivery_type`, `delivery_time`, `return_delivery_time`, `master_waybill_no`, `gain_integral`, `use_integral`, `back_integral`, `mark`, `is_del`, `remark`, `cost`, `verify_code`, `clerk_id`, `is_channel`, `is_system_del`, `update_time`, `delivery_code`, `type`, `pro_total_price`, `before_pay_price`, `is_alter_price`, `delivery_sf_status`, `return_sf_status`, `ums_mer_order_id`, `target_order_id`, `invoicing_status`, `user_type`, `apply_refund_type`, `closing_time`, `after_sales_deadline` FROM vca_order";

    public static final String selectOrderInfo = "SELECT `id`, `main_id`, `type`, `product_id`, `info`, `create_time`, `update_time`, `order_no`, `mer_order_no`, `name`, `attr_value_id`, `image`, `sku`, `price`, `pay_num`, `weight`, `volume`, `language`, `taoke_about_course_id`, `scheduling_id`, `scheduling_date`, `scheduling_start_time`, `scheduling_end_time`, `pay_price`, `refund_status`, `refund_reason_wap_img`, `refund_reason_wap_explain`, `refund_reason_wap`, `refund_reason`, `refund_reason_time`, `refund_application_time`, `refund_price`, `buyer`, `lecturer`, `is_get`, `write_off_clerk_id`, `write_off_time`, `ums_refund_order_id`, `status`, `credit_note_id`, `apply_refund_type`, `is_tip` FROM vca_order_info";

    public static final String selectUser = "SELECT `uid`, `avatar`,  `union_id`, `create_time`, `last_login_time`,  `pwd`, `user_register_type`, `status`, `mark`, `last_ip`, `now_money`, `integral`, `user_type`,  `adminid`, `login_type`, `update_time`, `clean_time`, `birthday`, `sex` FROM vca_user";

    public static void main(String[] args) {
        try {
            String tableNames = "vca_about,vca_cart,vca_category,vca_course,vca_course_about,vca_course_info,vca_course_package," +
                    "vca_course_package_relation,vca_course_scheduling,vca_course_type,vca_exhibition,vca_exhibition_info," +
                    "vca_exhibition_scheduling,vca_line_up,vca_order,vca_order_info,vca_product,vca_product_attr,vca_product_attr_result," +
                    "vca_product_attr_value,vca_product_info,vca_product_rule,vca_talk,vca_talk_info,vca_talk_scheduling,vca_user,vca_user_card," +
                    "vca_user_card_record,vca_user_collection,vca_user_token";
            List<String> tableNameList = Arrays.asList(tableNames.split(","));
            // 连接数据库
            Connection connection = DriverManager.getConnection("jdbc:mysql://rm-uf6y301ui3cfd5nt4io.mysql.rds.aliyuncs.com:3306/vca_dev?characterEncoding=utf-8&useSSL=false&serverTimeZone=GMT+8", "user_admin", "pzsjrnZ9Xjmy&qX");
            for (String tableName : tableNameList) {
                String beginDate = DateUtil.dateToStr(new Date(), Constants.DATE_FORMAT_START);
                String endDate = DateUtil.dateToStr(new Date(), Constants.DATE_FORMAT_END);
                String query = "SELECT * FROM `" + tableName + "`";
                if ("vca_order".equals(tableName)) {
                    query = selectOrder;
                }
                if ("vca_order_info".equals(tableName)) {
                    query = selectOrderInfo;
                }
                if ("vca_user".equals(tableName)) {
                    query = selectUser;
                }
                if ("vca_order".equals(tableName) || "vca_order_info".equals(tableName)) {
                    query += " where `update_time` BETWEEN '" + beginDate + "'AND '" + endDate + "'";
                }
                exportTableDataToCSV(tableName, connection, query);
            }
            connection.close();
            System.out.println("数据导出完成！");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void exportTableDataToCSV(String tableName, Connection connection, String query) throws SQLException, IOException {
        System.out.println(query);
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(query);
        ResultSetMetaData metaData = resultSet.getMetaData();
        int columnCount = metaData.getColumnCount();

        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet(tableName);
        CreationHelper creationHelper = workbook.getCreationHelper();

        Row headerRow = sheet.createRow(0);
        for (int i = 1; i <= columnCount; i++) {
            String columnName = metaData.getColumnName(i);
            Cell cell = headerRow.createCell(i - 1);
            cell.setCellValue(columnName);
        }

        int rowNum = 1;
        while (resultSet.next()) {
            Row row = sheet.createRow(rowNum++);

            for (int i = 1; i <= columnCount; i++) {
                Cell cell = row.createCell(i - 1);

                int columnType = metaData.getColumnType(i);
                if (columnType == java.sql.Types.INTEGER || columnType == java.sql.Types.FLOAT || columnType == java.sql.Types.DOUBLE) {
                    cell.setCellValue(resultSet.getDouble(i));
                } else {
                    cell.setCellValue(creationHelper.createRichTextString(resultSet.getString(i)));
                }
            }
        }

        FileOutputStream fileOutputStream = new FileOutputStream(tableName + ".csv");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        workbook.close();
    }

}

